CREATE OR REPLACE PACKAGE  pkg_debug
AS
/* ****************************************************************************************************************
   This package will log debug information into table CMART.DEBUG_LOG                             
   Call procedure SetTopic first, once is enough unless you want to change the debug topic, then
   followed by the procedure debug_log as many times as you want, just pass the log information as parameter.

   Before compile the package in any DB, make sure the following table already created:

	CREATE TABLE debug_log(
		   object_name  VARCHAR2 (30),
		   line         NUMBER (6,0),
		   topic        VARCHAR2 (36),
		   LOG          VARCHAR2 (1024),
		   elapsed      NUMBER,
		   runningtime  varchar2 (16),
		   logts        TIMESTAMP DEFAULT SYSTIMESTAMP,
		   SESSION_ID   NUMBER,
		   PROGRAM      VARCHAR2 (32),
		   HOST         VARCHAR2 (32),
		   DBUSER       VARCHAR2 (32),
		   OSUSER       VARCHAR2 (32)
		);
***************************************************************************************************************** */
   PROCEDURE setTopic (p_topic IN VARCHAR2);

   PROCEDURE debug_log (p_object_name IN VARCHAR2, p_line IN NUMBER,  p_log_txt IN VARCHAR2);
   
   PROCEDURE debug_log (p_log_txt IN VARCHAR2);
/* *****************************************************************************************************************  
   PROCEDURE access_log( ); -- log all HTTP/TCP/EXP activities for performance/status purpose 
   
   CREATE TABLE access_log (
		   access_category VARCHAR2(7 byte), --- valid values so far : HTTP, TCP, EXP 
		   
		   logts        TIMESTAMP DEFAULT SYSTIMESTAMP,
		   SESSION_ID   NUMBER,
		   PROGRAM      VARCHAR2 (32),
		   HOST         VARCHAR2 (32),
		   DBUSER       VARCHAR2 (32),
		   OSUSER       VARCHAR2 (32)
	);
***************************************************************************************************************** */	
END;
/









CREATE OR REPLACE PACKAGE BODY pkg_debug
AS
 
   g_init_time    PLS_INTEGER;
   g_start_time   PLS_INTEGER;

   g_session_id   NUMBER;

   g_topic        VARCHAR2 (36);
   g_host         VARCHAR2 (32);
   g_user         VARCHAR2 (32);
   g_osuser       VARCHAR2 (32);
   g_program      VARCHAR2 (50);

   PROCEDURE setTopic (p_topic IN VARCHAR2)
   IS
   BEGIN
      g_topic := p_topic;
	  g_init_time :=   DBMS_UTILITY.get_time;
   END;
   
   PROCEDURE debug_log (p_log_txt IN VARCHAR2)
   IS
   
   BEGIN
		debug_log ( 'ALL_LOGGING', NULL, p_log_txt);
   END;
/*
   FUNCTION  debug_flag ( p_object_name IN VARCHAR2) 
   RETURN BOOLEAN 
   --RESULT_CACHE RELIES_ON (cmart.look_up)
   AS 
        v_look_up_value CMART.LOOK_UP.LOOK_UP_VALUE%TYPE;
		v_ret BOOLEAN := FALSE;
   BEGIN
		SELECT   LOOK_UP_VALUE
		INTO     v_look_up_value
		FROM     look_up
		WHERE    LOOK_UP_NAME = p_object_name;
		
		IF v_look_up_value = 'Y' THEN 
			v_ret := TRUE;
		END IF;
		
		RETURN v_ret;
		
   EXCEPTION 
	   WHEN OTHERS THEN 
		  NULL;
		  RETURN v_ret;
   END debug_flag;
*/   
   PROCEDURE debug_log (p_object_name IN VARCHAR2, p_line IN NUMBER, p_log_txt IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      v_elapsed   PLS_INTEGER;
	  v_curtime   PLS_INTEGER;
	  v_runningtime varchar2(16);

   BEGIN
--	  IF NOT debug_flag(p_object_name) THEN 
--			RETURN ;
--	  END IF;
   
      v_curtime := DBMS_UTILITY.get_time;
	  v_elapsed := ( v_curtime - g_init_time)/100 ;
	  v_runningtime := to_char(floor(v_elapsed/3600), 'fm99')||':'||
	                 to_char(mod( floor(v_elapsed/60), 60),'fm99')||':'||
					 to_char(mod( floor(v_elapsed), 60), 'fm99') ;
      INSERT INTO debug_log (object_name, 
                                   line,
                                   topic,
                                   LOG,
                                   elapsed,
								   runningtime,
                                   SESSION_ID,
                                   PROGRAM,
                                   HOST,
                                   DBUSER,
                                   OSUSER)
           VALUES (p_object_name,
                   p_line,
                   g_topic,
                   p_log_txt,
                   v_curtime - g_start_time,
				   v_runningtime,
                   g_session_id,
                   g_program,
                   g_host,
                   g_user,
                   g_osuser);

      g_start_time := DBMS_UTILITY.get_time;
      COMMIT;
   END;

BEGIN
   IF g_topic IS NULL
   THEN
      g_topic := USER;
   END IF;

   g_start_time := DBMS_UTILITY.get_time;
   g_init_time  := DBMS_UTILITY.get_time ;

   g_session_id := SYS_CONTEXT ('USERENV', 'SESSIONID');
   g_host := SYS_CONTEXT ('USERENV', 'HOST');
   g_osuser := SYS_CONTEXT ('USERENV', 'OS_USER');
   g_program := SYS_CONTEXT ('USERENV', 'MODULE');
   g_user := USER;
END;
/
